<?php

namespace common\db;

use Yii;

class Db
{
    function __construct()
    {
    }

    function one($sql)
    {
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    function all($sql)
    {
        return Yii::$app->db->createCommand($sql)->queryAll();
    }

    public function insert($table, $data)
    {
        Yii::$app->db->createCommand()->insert($table, $data)->execute();
        return YII::$app->db->getLastInsertId();
    }

    public function update($table, $data, $where = '')
    {
        Yii::$app->db->createCommand()->update($table, $data, $where)->execute();
    }

    public function delete($table, $where)
    {
        Yii::$app->db->createCommand()->delete($table, $where)->execute();
    }

    public function count($sql)
    {
        $count = Yii::$app->db->createCommand($sql)
            ->queryScalar();
        return $count ? $count : 0;
    }

    public function paging($sql, $pageIndex, $pageCount)
    {
        $sql=str_replace(array("\r\n", "\r", "\n"), " ", $sql);

        //替换原SQL select 字段部分 为 count(*) as num
        $arr = explode(' ', $sql);
        $flag = true;
        $num = 0;
        $countSql = 'select count(*) as num ';
        foreach ($arr as $i => $a) {
            $a = str_replace(' ', '', $a);

            preg_match_all('/\(/i', $a, $mh1);
            $num += count($mh1[0]);

            preg_match_all('/\)/i', $a, $mh2);
            $num -= count($mh2[0]);

            $a = trim($a);
            if (strtolower($a) == 'from' && $num === 0 && $flag) {
                $flag = false;
            }
            if (!$flag)
                $countSql .= $a . ' ';
        }

        $result = ['count' => 0, 'data' => []];
        $count = Yii::$app->db->createCommand($countSql)
            ->queryScalar();
        $result['count'] = $count ? $count : 0;

        $limit = ($pageIndex - 1) * $pageCount . ',' . $pageCount;
        $sql .= ' limit ' . $limit;


        $data = Yii::$app->db->createCommand($sql)
            ->queryAll();

        $result['data'] = $data;
        $result['size'] = (int)$pageCount;
        $result['pages'] = ceil($result['count'] / $pageCount);
        $result['index'] = (int)$pageIndex;

        return $result;
    }


    public function page($sql, $pageIndex, $pageCount)
    {
        $sql=str_replace(array("\r\n", "\r", "\n"), " ", $sql);

        //替换原SQL select 字段部分 为 count(*) as num
        $countSql = "select count(*) as num from ($sql) as a";
        $result = ['count' => 0, 'data' => []];
        $count = Yii::$app->db->createCommand($countSql)
            ->queryScalar();
        $result['count'] = $count ? $count : 0;

        $limit = ($pageIndex - 1) * $pageCount . ',' . $pageCount;
        $sql .= ' limit ' . $limit;


        $data = Yii::$app->db->createCommand($sql)
            ->queryAll();

        $result['data'] = $data;
        $result['size'] = (int)$pageCount;
        $result['pages'] = ceil($result['count'] / $pageCount);
        $result['index'] = (int)$pageIndex;

        return $result;
    }
}
